<?php
    include(dirname(__FILE__)."/../include/apility/apility.php");
    include(dirname(__FILE__)."/../Dao/db.php");

    class GetReportData
    {
        //メンバ
        var $allAccounts;
        //アカントの情報
        var $authenticationContext;
        //例外の情報処理対象
        var $faultStack;
        //レポートデータの日付
        var $reportDate;
        //曜日
        var $dayOfTheWeek;
        //今週の月曜日
        var $weekly;

        function GetReportData()
        {
            //全部のメンバを取得
            $this->allAccounts = $this->getAllAccounts();
            //例外処理対象を取得
            $this->faultStack = &APIlityFault::getFaultStack();
            //アカントの情報設定
            $this->authenticationContext = &APIlityAuthentication::getContext();
            //レポートデータの日付設定
            $this->reportDate = date("Y-m-d",strtotime("-20 minute"));
			//$this->reportDate = '2009-02-17';
			echo "レポート日付：".$this->reportDate."\r\n";
            //週の開始日を計算
            $this->dayOfTheWeek = date("w",strtotime($this->reportDate));
            if($this->dayOfTheWeek == 1)
            {
                $this->weekly = date("Ymd",strtotime("Monday",strtotime($this->reportDate)));
            }
            else
            {
                $this->weekly = date("Ymd",strtotime("last Monday",strtotime($this->reportDate)));
            }
        }

        function getAllAccounts()
        {
            $allAccounts = array();
            $query = "SELECT * FROM account WHERE status=1";
            $result = mysql_query($query);
            while($row=mysql_fetch_array($result))
            {
                array_push($allAccounts,$row);
            }
            return $allAccounts;
        }

        function run()
        {
            echo date("Y-m-d H:i:s")."　レポートデータバッチ開始\r\n\r\n";
            //アカント一つずつに対処する
            foreach($this->allAccounts as $account)
            {
                echo date("Y-m-d H:i:s")."　".$account['account_name']."　開始\r\n";
                $memberId = $account['memberId'];
                //GoogleAdwordsのロジック
                if($account['type'] == 1)
                {
                    echo "媒体種類：GoogleAdwords\r\n";
                    //クライアントアカント設定
                    if(!$this->setContext($account['account_name']))
                    {
                        echo date("Y-m-d H:i:s")."　".$account['account_name']."　終了\r\n\r\n";
                        continue;
                    }

                    //広告グループのレポートデータを取得
                    echo date("Y-m-d H:i:s")."　広告グループレポートデータダウンロード開始\r\n";
                    $adGroupReport = $this->getAdGroupReport($this->reportDate);
                    if(!$adGroupReport)
                    {
                        echo date("Y-m-d H:i:s")."　レポートデータを取得する時、エラーは発生しまいました";
                        $fault = array_pop($this->faultStack);
                        if(isset($fault))
                        {
                            $fault->printFault();
                        }
                        else
                        {
                            echo "未知エラー。\r\n";
                        }
                        echo date("Y-m-d H:i:s")."　".$account['account_name']."　終了\r\n\r\n";
                        continue;
                    }
                    echo date("Y-m-d H:i:s")."　広告グループレポートデータダウンロード完成\r\n";

                    //レポートデータをローカルデータベースに入れる
                    echo date("Y-m-d H:i:s")."　ローカルデータベースに挿入開始\r\n";
                    
                    if(!$this->insertAdGroupReport($adGroupReport,$memberId))
                    {
                        echo date("Y-m-d H:i:s")."　挿入失敗しまいました\r\n";
                        echo date("Y-m-d H:i:s")."　".$account['account_name']."　終了\r\n\r\n";
                        continue;
                    }
                    echo date("Y-m-d H:i:s")."　挿入成功しました\r\n";

                    //クリエイティブのレポートデータを取得
                    echo date("Y-m-d H:i:s")."　クリエイティブレポートデータダウンロード開始\r\n";
                    $creativeReport = $this->getCreativeReport($this->reportDate);
                    if(!$creativeReport)
                    {
                        echo date("Y-m-d H:i:s")."　レポートデータを取得する時、エラーは発生しまいました";
                        $fault = array_pop($this->faultStack);
                        if(isset($fault))
                        {
                            $fault->printFault();
                        }
                        else
                        {
                            echo "未知エラー。\r\n";
                        }
                        echo date("Y-m-d H:i:s")."　".$account['account_name']."　終了\r\n\r\n";
                        continue;
                    }
                    echo date("Y-m-d H:i:s")."　クリエイティブレポートデータダウンロード完成\r\n";

                    //取得したデータをローカルデータベースに入れる
                    echo date("Y-m-d H:i:s")."　ローカルデータベースに挿入開始\r\n";
                    if(!$this->insertCreativeReport($creativeReport,$memberId))
                    {
                        echo date("Y-m-d H:i:s")."　挿入失敗しまいました\r\n";
                        echo date("Y-m-d H:i:s")."　".$account['account_name']."　終了\r\n\r\n";
                        continue;
                    }
                    echo date("Y-m-d H:i:s")."　挿入成功しました\r\n";

                    //キーワードのレポートデータを取得
                    echo date("Y-m-d H:i:s")."　キーワードレポートデータダウンロード開始\r\n";
                    $keywordReport = $this->getKeywordReport($this->reportDate);
                    if(!$keywordReport)
                    {
                        echo date("Y-m-d H:i:s")."　レポートデータを取得する時、エラーは発生しまいました";
                        $fault = array_pop($this->faultStack);
                        if(isset($fault))
                        {
                            $fault->printFault();
                        }
                        else
                        {
                            echo "未知エラー。\r\n";
                        }
                        echo date("Y-m-d H:i:s")."　".$account['account_name']."　終了\r\n\r\n";
                        continue;
                    }
                    echo date("Y-m-d H:i:s")."　キーワードレポートデータダウンロード完成\r\n";

                    //データベースに入れる
                    echo date("Y-m-d H:i:s")."　ローカルデータベースに挿入開始\r\n";
                    if(!$this->insertKeywordReport($keywordReport,$memberId))
                    {
                        echo date("Y-m-d H:i:s")."　挿入失敗しまいました\r\n";
                        echo date("Y-m-d H:i:s")."　".$account['account_name']."　終了\r\n\r\n";
                        continue;
                    }
                    echo date("Y-m-d H:i:s")."　挿入成功しました\r\n";
                }
                echo date("Y-m-d H:i:s")."　".$account['account_name']."　終了\r\n\r\n";
            }
            echo date("Y-m-d H:i:s")."　レポートデータバッチ終了\r\n";
        }

        function setContext($ClientEmail)
        {
            //マネッジャのアカント設定
            $this->authenticationContext->setClientEmail($ClientEmail);
            if(!getAccountInfo())
            {
                $fault = array_pop($this->faultStack);
                if(isset($fault))
                {
                    $fault->printFault();
                }
                echo date("Y-m-d H:i:s")."　該当クライアントアカントはクライアントセンターに管理しないかもしれません。或いはネット問題です。ご確認ください。\r\n";
                return false;
            }
            return true;
        }

        function getAdGroupReport($reportDate)
        {
            $adGroupReport = getAdGroupXmlReport(   'AdGroupReport',
                                                    $reportDate,
                                                    $reportDate,
                                                    array('AdGroupId','Impressions','Clicks','Cost'),
                                                    array('Daily'),
                                                    array(),
                                                    array(),
                                                    array(),
                                                    array(),
                                                    array(),
                                                    array(),
                                                    '',
                                                    '',
                                                    false,
                                                    array(),
                                                    false,
                                                    30,
                                                    false
                                                    );
            return $adGroupReport;
        }

        function insertAdGroupReport($adGroupReport,$memberId)
        {
            // PHP version is >= 5, i.e. only DOM is avalable
            if (version_compare(phpversion(), "5.0.0", ">="))
            {
                $reportRows = $adGroupReport->getElementsByTagName("row");
            }
            // PHP version is <5, i.e. only DOM XML is available
            else
            {
                $reportRows = $adGroupReport->get_elements_by_tagname("row");
            }
            foreach($reportRows as $reportRow)
            {
                // PHP version is >= 5, i.e. only DOM is avalable
                if (version_compare(phpversion(), "5.0.0", ">="))
                {
                    $adGroupId = $reportRow->getAttribute("adgroupid");
                    $imps = $reportRow->getAttribute("imps");
                    $clicks = $reportRow->getAttribute("clicks");
                    $cost = $reportRow->getAttribute("cost");
                }
                // PHP version is <5, i.e. only DOM XML is available
                else
                {
                    $adGroupId = $reportRow->get_attribute("adgroupid");
                    $imps = $reportRow->get_attribute("imps");
                    $clicks = $reportRow->get_attribute("clicks");
                    $cost = $reportRow->get_attribute("cost");
                }

                //ローカルメンバID、キャンペーンID、広告グループIDを検索
                $query = "SELECT * FROM adgroup WHERE ";
                //GoogleAdwordsのレポート
                $query .= "listingType=1 AND ";
                $query .= "memberId=$memberId AND ";
                $query .= "adGroupId=$adGroupId AND ";
                $query .= "status != 9";
                $result = mysql_query($query);
                $row = mysql_fetch_array($result);
                //Googleからの広告グループはローカルデータベースに存在しない場合、エラーになる
                if(!$row)
                {
                    echo $query;
                    echo "広告グループID[$adGroupId]はローカルデータベースに管理しないかもしれません\r\n";
                    continue;
                }

                $camp_Id = $row['camp_Id'];
                $adGr_Id = $row['adGr_Id'];

                //旧なレコードを削除
                $query = "DELETE FROM adreport WHERE ";
                $query .= "date='$this->reportDate' AND ";
                $query .= "memberId=$memberId AND ";
                $query .= "adGroup_Id=$adGr_Id";
                if(!mysql_query($query))
                {
                    echo $query;
                    echo "\r\n";
                    echo mysql_errno() . ": " . mysql_error() . "\r\n";
                    return false;
                }

                //レポートテーブルに保存
                $query = "INSERT INTO adreport (";
                $query .= "date,memberId,listingType,campaign_Id,adGroup_Id,";
                $query .= "weekly,dayOfTheWeek,status,impression,clickCnt,cost,";
                $query .= "registDate,lastUpdate) VALUES (";
                $query .= "'$this->reportDate',";
                $query .= "$memberId,";
                $query .= "1,";
                $query .= "$camp_Id,";
                $query .= "$adGr_Id,";
                $query .= "$this->weekly,";
                $query .= "$this->dayOfTheWeek,";
                //このステータスいまは使いません。３で設定しておく
                $query .= "3,";
                $query .= "$imps,";
                $query .= "$clicks,";
                $query .= "$cost,";
                $query .= "now(),now());";
                if(!mysql_query($query))
                {
                    echo $query;
                    echo "\r\n";
                    return false;
                }
            }
            //クリック数無しレコードを登録する
            $query = "SELECT  a.memberId,a.camp_Id,a.adGr_Id ";
            $query .= "FROM   adgroup a ";
            $query .= "LEFT JOIN   adreport b ";
            $query .= "ON     a.adGr_Id=b.adGroup_Id ";
            $query .= "AND    b.date='$this->reportDate' ";
            $query .= "WHERE  a.status <> 9 ";
            $query .= "AND    b.adGroup_Id IS NULL ";
            $result = mysql_query($query);
            while($row=mysql_fetch_array($result))
            {
                $query = "INSERT INTO adreport (";
                $query .= "date,memberId,listingType,campaign_Id,adGroup_Id,";
                $query .= "weekly,dayOfTheWeek,status,impression,clickCnt,cost,";
                $query .= "registDate,lastUpdate) VALUES (";
                $query .= "'$this->reportDate',";
                $query .= "{$row['memberId']},";
                $query .= "1,";
                $query .= "{$row['camp_Id']},";
                $query .= "{$row['adGr_Id']},";
                $query .= "$this->weekly,";
                $query .= "$this->dayOfTheWeek,";
                //このステータスいまは使いません。３で設定しておく
                $query .= "3,";
                $query .= "0,";
                $query .= "0,";
                $query .= "0,";
                $query .= "now(),now());";
                if(!mysql_query($query))
                {
                    echo $query;
                    echo "\r\n";
                    return false;
                }
            }
            return true;
        }


        function getCreativeReport($reportDate)
        {
            $creativeReport = getCreativeXmlReport( 'CreativeReport',
                                  $reportDate,
                                  $reportDate,
                                    array('CreativeId','Impressions','Clicks','Cost','AveragePosition'),
                                    array('Daily'),
                                    array(),
                                    array(),
                                    array(),
                                    array(),
                                    array(),
                                    array(),
                                      '',
                                      '',
                                    false,
                                    array(),
                                    false,
                                    30,
                                    false
                                  );
            return $creativeReport;
        }

        function insertCreativeReport($creativeReport,$memberId)
        {
            // PHP version is >= 5, i.e. only DOM is avalable
            if (version_compare(phpversion(), "5.0.0", ">="))
            {
                $reportRows = $creativeReport->getElementsByTagName("row");
            }
            // PHP version is <5, i.e. only DOM XML is available
            else
            {
                $reportRows = $creativeReport->get_elements_by_tagname("row");
            }
            foreach($reportRows as $reportRow)
            {
                // PHP version is >= 5, i.e. only DOM is avalable
                if (version_compare(phpversion(), "5.0.0", ">="))
                {
                    $creativeId = $reportRow->getAttribute("creativeid");
                    $imps = $reportRow->getAttribute("imps");
                    $clicks = $reportRow->getAttribute("clicks");
                    $cost = $reportRow->getAttribute("cost");
                    $pos = $reportRow->getAttribute("pos");
                }
                // PHP version is <5, i.e. only DOM XML is available
                else
                {
                    $creativeId = $reportRow->get_attribute("creativeid");
                    $imps = $reportRow->get_attribute("imps");
                    $clicks = $reportRow->get_attribute("clicks");
                    $cost = $reportRow->get_attribute("cost");
                    $pos = $reportRow->get_attribute("pos");
                }
                //ローカルメンバID、キャンペーンID、広告グループIDを検索
                $query = "SELECT * FROM creative WHERE ";
                //GoogleAdwordsのレポート
                $query .= "listingType=1 AND ";
                $query .= "memberId=$memberId AND ";
                $query .= "creativeId=$creativeId AND ";
                $query .= "status != 9";
                $result = mysql_query($query);
                $row = mysql_fetch_array($result);
                //GoogleからのクリエイティブIDはローカルデータベースに存在しない場合、エラーになる
                if(!$row)
                {
                    echo $query;
                    echo "クリエイティブID[$creativeId]はローカルデータベースに管理しないかもしれません\r\n";
                    continue;
                }
                $camp_Id = $row['camp_Id'];
                $adGr_Id = $row['adGr_Id'];
                $Crt_Id = $row['Crt_Id'];

                //旧なレコードを削除
                $query = "DELETE FROM creativereport WHERE ";
                $query .= "date='$this->reportDate' AND ";
                $query .= "memberId=$memberId AND ";
                $query .= "creative_Id=$Crt_Id;";
                if(!mysql_query($query))
                {
                    echo $query;
                    echo "\r\n";
                    return false;
                }

                //レポートテーブルに保存
                $query = "INSERT INTO creativereport (";
                $query .= "date,memberId,listingType,campaign_Id,adGroup_Id,creative_Id,";
                $query .= "weekly,dayOfTheWeek,status,impression,clickCnt,cost,position,";
                $query .= "registDate,lastUpdate) VALUES (";
                $query .= "'$this->reportDate',";
                $query .= "$memberId,";
                $query .= "1,";
                $query .= "$camp_Id,";
                $query .= "$adGr_Id,";
                $query .= "$Crt_Id,";
                $query .= "$this->weekly,";
                $query .= "$this->dayOfTheWeek,";
                //このステータスいまは使いません。３で設定しておく
                $query .= "3,";
                $query .= "$imps,";
                $query .= "$clicks,";
                $query .= "$cost,";
                $query .= "$pos,";
                $query .= "now(),now())";
                if(!mysql_query($query))
                {
                    echo $query;
                    echo "\r\n";
                    echo mysql_errno() . ": " . mysql_error() . "\r\n";
                    return false;
                }
            }
            //クリック数無しレコードを登録する
            $query = "SELECT  a.memberId,a.camp_Id,a.adGr_Id,a.Crt_Id ";
            $query .= "FROM   creative a ";
            $query .= "LEFT JOIN   creativereport b ";
            $query .= "ON     a.Crt_Id=b.creative_Id ";
            $query .= "AND    b.date='$this->reportDate' ";
            $query .= "WHERE  a.status <> 9 ";
            $query .= "AND    b.creative_Id IS NULL";
            $result = mysql_query($query);
            while($row=mysql_fetch_array($result))
            {
                $query = "INSERT INTO creativereport (";
                $query .= "date,memberId,listingType,campaign_Id,adGroup_Id,creative_Id,";
                $query .= "weekly,dayOfTheWeek,status,impression,clickCnt,cost,position,";
                $query .= "registDate,lastUpdate) VALUES (";
                $query .= "'$this->reportDate',";
                $query .= "{$row['memberId']},";
                $query .= "1,";
                $query .= "{$row['camp_Id']},";
                $query .= "{$row['adGr_Id']},";
                $query .= "{$row['Crt_Id']},";
                $query .= "$this->weekly,";
                $query .= "$this->dayOfTheWeek,";
                //このステータスいまは使いません。３で設定しておく
                $query .= "3,";
                $query .= "0,";
                $query .= "0,";
                $query .= "0,";
                $query .= "0,";
                $query .= "now(),now())";
                if(!mysql_query($query))
                {
                    echo $query;
                    echo "\r\n";
                    echo mysql_errno() . ": " . mysql_error() . "\r\n";
                    return false;
                }
            }
            return true;
        }

        function getKeywordReport($reportDate)
        {
            $keywordReport = getKeywordXmlReport( 'KeywordReport',
                                                  $reportDate,
                                                  $reportDate,
                                                  array('KeywordId','Impressions','Clicks','Cost','AveragePosition'),
                                                  array('Daily'),
                                                  array(),
                                                  array(),
                                                  array(),
                                                  array(),
                                                  array(),
                                                  array(),
                                                  '',
                                                  '',
                                                  false,
                                                  array(),
                                                  false,
                                                  30,
                                                  false
                                                );
            return $keywordReport;
        }

        function insertKeywordReport($keywordReport,$memberId)
        {
            // PHP version is >= 5, i.e. only DOM is avalable
            if (version_compare(phpversion(), "5.0.0", ">="))
            {
                $reportRows = $keywordReport->getElementsByTagName("row");
            }
            // PHP version is <5, i.e. only DOM XML is available
            else
            {
                $reportRows = $keywordReport->get_elements_by_tagname("row");
            }
            foreach($reportRows as $reportRow)
            {
                // PHP version is >= 5, i.e. only DOM is avalable
                if (version_compare(phpversion(), "5.0.0", ">="))
                {
                    $keywordId = $reportRow->getAttribute("keywordid");
                    $imps = $reportRow->getAttribute("imps");
                    $clicks = $reportRow->getAttribute("clicks");
                    $cost = $reportRow->getAttribute("cost");
                    $pos = $reportRow->getAttribute("pos");
                }
                // PHP version is <5, i.e. only DOM XML is available
                else
                {
                    $keywordId = $reportRow->get_attribute("keywordid");
                    $imps = $reportRow->get_attribute("imps");
                    $clicks = $reportRow->get_attribute("clicks");
                    $cost = $reportRow->get_attribute("cost");
                    $pos = $reportRow->get_attribute("pos");
                }
                //ローカルメンバID、キャンペーンID、広告グループIDを検索
                $query = "SELECT * FROM keyword WHERE ";
                //GoogleAdwordsのレポート
                $query .= "listingType=1 AND ";
                $query .= "memberId=$memberId AND ";
                $query .= "keywordId=$keywordId AND ";
                $query .= "status != 9";
                $result = mysql_query($query);
                $row = mysql_fetch_array($result);
                //GoogleからのキーワードIDはローカルデータベースに存在しない場合、エラーになる
                if(!$row)
                {
//                    echo $query;
//                    echo "キーワードID[$keywordId]はローカルデータベースに管理しないかもしれません\r\n";
                    continue;
                }
                $camp_Id = $row['camp_Id'];
                $adGr_Id = $row['adGr_Id'];
                $key_Id = $row['key_Id'];

                //旧なレコードを削除
                $query = "DELETE FROM keywordsreport WHERE ";
                $query .= "date='$this->reportDate' AND ";
                $query .= "memberId=$memberId AND ";
                $query .= "keyword_Id=$key_Id;";
                if(!mysql_query($query))
                {
                    echo $query;
                    echo "\r\n";
                    return false;
                }

                //レポートテーブルに保存
                $query = "INSERT INTO keywordsreport (";
                $query .= "date,memberId,listingType,campaign_Id,adGroup_Id,keyword_Id,";
                $query .= "weekly,dayOfTheWeek,status,impression,clickCnt,cost,position,";
                $query .= "registDate,lastUpdate) VALUES (";
                $query .= "'$this->reportDate',";
                $query .= "$memberId,";
                $query .= "1,";
                $query .= "$camp_Id,";
                $query .= "$adGr_Id,";
                $query .= "$key_Id,";
                $query .= "$this->weekly,";
                $query .= "$this->dayOfTheWeek,";
                //このステータスいまは使いません。３で設定しておく
                $query .= "3,";
                $query .= "$imps,";
                $query .= "$clicks,";
                $query .= "$cost,";
                $query .= "$pos,";
                $query .= "now(),now());";
                if(!mysql_query($query))
                {
                    echo $query;
                    echo "\r\n";
                    echo mysql_errno() . ": " . mysql_error() . "\r\n";
                    return false;
                }
            }
            //クリック数無しレコードを登録する
            $query = "SELECT  a.memberId,a.camp_Id,a.adGr_Id,a.key_Id ";
            $query .= "FROM   keyword a ";
            $query .= "LEFT JOIN   keywordsreport b ";
            $query .= "ON    a.key_Id=b.keyword_Id ";
            $query .= "AND    b.date='$this->reportDate' ";
            $query .= "WHERE  a.status <> 9 ";
            $query .= "AND    b.keyword_Id IS NULL";
            $result = mysql_query($query);
            while($row=mysql_fetch_array($result))
            {
                $query = "INSERT INTO keywordsreport (";
                $query .= "date,memberId,listingType,campaign_Id,adGroup_Id,keyword_Id,";
                $query .= "weekly,dayOfTheWeek,status,impression,clickCnt,cost,position,";
                $query .= "registDate,lastUpdate) VALUES (";
                $query .= "'$this->reportDate',";
                $query .= "{$row['memberId']},";
                $query .= "1,";
                $query .= "{$row['camp_Id']},";
                $query .= "{$row['adGr_Id']},";
                $query .= "{$row['key_Id']},";
                $query .= "$this->weekly,";
                $query .= "$this->dayOfTheWeek,";
                //このステータスいまは使いません。３で設定しておく
                $query .= "3,";
                $query .= "0,";
                $query .= "0,";
                $query .= "0,";
                $query .= "0,";
                $query .= "now(),now());";
                if(!mysql_query($query))
                {
                    echo $query;
                    echo "\r\n";
                    echo mysql_errno() . ": " . mysql_error() . "\r\n";
                    return false;
                }
            }
            return true;
        }
    }

$instance = new GetReportData();
$instance->run();

?>
